﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_RemainStoreEstimate : UserControl
    {
        
        public panel_RemainStoreEstimate()
        {
            InitializeComponent();
            LoadData();
            RemainStoreEstimate.PopupMenuShowing += RemainStoreEstimate_PopupMenuShowing;
            remainStoreContext.ItemClicked += remainStoreContext_ItemClicked;
        }

        void remainStoreContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.remainStoreContext.Hide();
            switch (e.ClickedItem.Name)
            {                
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void RemainStoreEstimate_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            remainStoreContext.Show(MousePosition);            
        }

        //////////////////////////////////////////////
        /// Load Data to Grid View
        //////////////////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT [241_PODetails].POID, [241_PODetails].OrderNo, [241_PODetails].PartNo, [231_F2Process].PPartNo,"
                    + " [241_PODetails].DeliveryDate, [241_PODetails].TransportID, [241_PODetails].DeliveryTermID,"
                    + " [222_DeliveryTerm].HandOverPlace,"
                    + " CASE [222_DeliveryTerm].HandOverPlace"
                    + " WHEN 'RRC' THEN 0 WHEN 'CUS' THEN [222_Transport].EstimateTime END"
                    + " AS TransportTime,"
                    + " CASE WHEN [231_F2Process].PPartNo IS NULL THEN 0 ELSE 7 END AS F2time," 
                    + " [241_PODetails].DeliveryDate +"
                    + " CASE [222_DeliveryTerm].HandOverPlace"
                    + " WHEN 'RRC' THEN 0 WHEN 'CUS' THEN [222_Transport].EstimateTime END"
                    +     " + CASE WHEN [231_F2Process].PPartNo IS NULL THEN 0 ELSE 7 END AS StoreDeadline,"
                    + " SUM(dbo.[241_PODetails].Quantity) - ISNULL(SUM(dbo.[241_PartReceive].Quantity), 0)"
                    + " + ISNULL(SUM(dbo.[241_POMistake].Quantity), 0) - ISNULL(SUM(dbo.[241_POMistakeReturn].Quantity), 0)"
                    + " AS TotalRemain" 		
                    + " FROM [241_PODetails] LEFT OUTER JOIN"
                    + " [222_Transport] ON [222_Transport].TransportID = [241_PODetails].TransportID LEFT OUTER JOIN"
                    + " [241_PO] ON [241_PODetails].POID = [241_PO].ID LEFT OUTER JOIN"
                    + " [231_F2Process] ON [241_PO].SupplierID = [231_F2Process].SupplierID"
                    + " AND [241_PODetails].PartNo = [231_F2Process].PartNo COLLATE Japanese_Unicode_CI_AI LEFT OUTER JOIN"
                    + " [222_DeliveryTerm] ON [241_PODetails].DeliveryTermID = [222_DeliveryTerm].DeliveryTermID LEFT OUTER JOIN"
                    + " [241_PartReceive] ON [241_PODetails].POID = [241_PartReceive].POID"
                    + " AND [241_PODetails].OrderNo = [241_PartReceive].OrderNo COLLATE Japanese_Unicode_CI_AI LEFT OUTER JOIN"
                    + " [241_POMistake] ON [241_PODetails].POID = [241_POMistake].POID"
                    + " AND [241_PODetails].OrderNo = [241_POMistake].OrderNo LEFT OUTER JOIN"
                    + " [241_POMistakeReturn] ON [241_PODetails].POID = [241_POMistakeReturn].POID"
                    + " AND [241_PODetails].OrderNo = [241_POMistakeReturn].OrderNo"	
                    + " GROUP BY [241_PODetails].POID, [241_PODetails].OrderNo, [241_PODetails].PartNo, [231_F2Process].PPartNo,"
                    + " [241_PODetails].DeliveryDate, [241_PODetails].TransportID, [222_Transport].EstimateTime,"
                    + " [241_PODetails].DeliveryTermID, [222_DeliveryTerm].HandOverPlace"
                    + " HAVING ((SUM([241_PODetails].Quantity) - ISNULL(SUM([241_PartReceive].Quantity), 0)"
                    + " + ISNULL(SUM([241_POMistake].Quantity), 0) - ISNULL(SUM([241_POMistakeReturn].Quantity), 0)) > 0)";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridRemainStoreEstimate.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("POID", typeof(string));
                newTable.Columns.Add("OrderNo", typeof(string));
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("PPartNo", typeof(string));
                newTable.Columns.Add("DeliveryDate", typeof(DateTime));
                newTable.Columns.Add("TransportID", typeof(string));
                newTable.Columns.Add("DeliveryTermID", typeof(string));
                newTable.Columns.Add("HandOverPlace", typeof(string));
                newTable.Columns.Add("TransportTime", typeof(int));
                newTable.Columns.Add("F2Time", typeof(int));
                newTable.Columns.Add("StoreDeadline", typeof(DateTime));
                newTable.Columns.Add("TotalRemain", typeof(int));
                gridRemainStoreEstimate.DataSource = newTable;
            }
            RemainStoreEstimate.Columns[0].Caption = "PO ID";
            RemainStoreEstimate.Columns[1].Caption = "Order No";
            RemainStoreEstimate.Columns[2].Caption = "Part No";
            RemainStoreEstimate.Columns[3].Caption = "Pro Part No";
            RemainStoreEstimate.Columns[4].Caption = "Delivery Date";
            RemainStoreEstimate.Columns[5].Caption = "Transport ID";
            RemainStoreEstimate.Columns[6].Caption = "Delivery Term ID";
            RemainStoreEstimate.Columns[7].Caption = "Hand Over Place";
            RemainStoreEstimate.Columns[8].Caption = "Transport Time";
            RemainStoreEstimate.Columns[9].Caption = "F2 Time";
            RemainStoreEstimate.Columns[10].Caption = "Store Input (Est)";
            RemainStoreEstimate.Columns[11].Caption = "Remain Quantity";            
            RemainStoreEstimate.BestFitColumns();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }
    }
}
